﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using ebay.Dal.CenterDB_LTableAdapters;
using ebay.Dal;
using ebay.Dal.CenterDB_CTableAdapters;
using System.Web;
using ebay.Dal.CenterDB_OrderTableAdapters;

namespace ebay.Dal
{
    public class Report
    {
        
        #region 坏品统计

        //获得数据集
        public DataTable BadProductCount()
        {       
            string Thirtydaystest = @"Select FitemId ,ThirtyBadFQty,ThirtySaleFQty ,ThirtyRate,ThirtyOrderId  from V_BP1";//30天数据集，

            string sixtydaystext = @"Select FitemId ,sixtyBadFQty ,sixtySaleFQty,sixtyRate ,sixtyOrderId  from V_BP2";//60天数据集

            string fardaystext = @"Select FitemId ,farBadFQty ,farSaleFQty,farRate ,farOrderId  from V_BP3";//迄今数据集
            
            string fitemidtext = @"select FitemId from(
                                     select V_BP1.FitemId as FitemId from V_BP1)a 
                                     Union
                                     (select V_BP2.FitemId as FitemId from V_BP2)  
                                     Union 
                                    (select V_BP3.FitemId as FitemId from V_BP3)";//获得主键“产品编号”

            //获得数据集
            DataTable dtid = SqlHelper.ExecuteDataTable(SqlHelper.ceterDB_Conn, CommandType.Text, fitemidtext, null); //产品编号集
            DataTable dt1 = SqlHelper.ExecuteDataTable(SqlHelper.ceterDB_Conn, CommandType.Text, Thirtydaystest, null);//前30天数据集合
            DataTable dt2 = SqlHelper.ExecuteDataTable(SqlHelper.ceterDB_Conn, CommandType.Text, sixtydaystext, null); //前60天数据集合
            DataTable dt3 = SqlHelper.ExecuteDataTable(SqlHelper.ceterDB_Conn, CommandType.Text, fardaystext, null);   //迄今数据集合

            DataTable datacount = new DataTable();//创建存储数据表  
         
            datacount.Columns.Add(new DataColumn("商品编号", typeof(string)));
            datacount.Columns.Add(new DataColumn("30天坏品数", typeof(int)));
            datacount.Columns.Add(new DataColumn("30天销量", typeof(int)));
            datacount.Columns.Add(new DataColumn("30天坏品率", typeof(float)));
            datacount.Columns.Add(new DataColumn("30天订单编号", typeof(string)));
            datacount.Columns.Add(new DataColumn("60天坏品数", typeof(int)));
            datacount.Columns.Add(new DataColumn("60天销量", typeof(int)));
            datacount.Columns.Add(new DataColumn("60天坏品率", typeof(float)));
            datacount.Columns.Add(new DataColumn("60天订单编号", typeof(string)));
            datacount.Columns.Add(new DataColumn("迄今坏品数", typeof(int)));
            datacount.Columns.Add(new DataColumn("迄今销量", typeof(int)));
            datacount.Columns.Add(new DataColumn("迄今坏品率", typeof(float)));
            datacount.Columns.Add(new DataColumn("迄今订单编号", typeof(string)));
            datacount.Columns.Add(new DataColumn("等级", typeof(string)));
           
            //为datacount 表填充数据
            for (int i = 0; i < dtid.Rows.Count; i++)
            {
                DataRow rw = datacount.NewRow();
                rw["商品编号"] = dtid.Rows[i]["FitemId"].ToString();
                //填充前30天数据集              
                for (int j = 0; j < dt1.Rows.Count; j++)
                {
                    if (dtid.Rows[i][0].ToString() == dt1.Rows[j]["FitemId"].ToString())
                    {
                        rw["30天坏品数"] = Convert.ToDecimal(dt1.Rows[j]["ThirtyBadFQty"].ToString());
                        rw["30天销量"] = Convert.ToInt32(dt1.Rows[j]["ThirtySaleFQty"].ToString());
                        rw["30天坏品率"] = Convert.ToDouble(dt1.Rows[j]["ThirtyRate"].ToString());
                        rw["30天订单编号"] = dt1.Rows[j]["ThirtyOrderId"].ToString();
                        break;
                    }
                    else
                    {
                        rw["30天坏品数"] = 0;
                        rw["30天销量"] = 0;
                        rw["30天坏品率"] = 0;
                        rw["30天订单编号"] = "0";
                    }
                }

                //填充前60天数据集              
                for (int j = 0; j < dt2.Rows.Count; j++)
                {
                    if (dtid.Rows[i][0].ToString() == dt2.Rows[j]["FitemId"].ToString())
                    {
                        rw["60天坏品数"] = Convert.ToDecimal(dt2.Rows[j]["sixtyBadFQty"].ToString());
                        rw["60天销量"] = Convert.ToInt32(dt2.Rows[j]["sixtySaleFQty"].ToString());
                        rw["60天坏品率"] = Convert.ToDouble(dt2.Rows[j]["sixtyRate"].ToString());
                        rw["60天订单编号"] = dt2.Rows[j]["sixtyOrderId"].ToString();
                        break;
                    }
                    else
                    {
                        rw["60天坏品数"] = 0;
                        rw["60天销量"] = 0;
                        rw["60天坏品率"] = 0;
                        rw["60天订单编号"] = "0";
                    }
                }
                //填充迄今数据集              
                for (int j = 0; j < dt3.Rows.Count; j++)
                {
                    if (dtid.Rows[i][0].ToString() == dt3.Rows[j]["FitemId"].ToString())
                    {
                        rw["迄今坏品数"] = Convert.ToDecimal(dt3.Rows[j]["farBadFQty"].ToString());
                        rw["迄今销量"] = Convert.ToInt32(dt3.Rows[j]["farSaleFQty"].ToString());
                        rw["迄今坏品率"] = Convert.ToDouble(dt3.Rows[j]["farRate"].ToString());
                        rw["迄今订单编号"] = dt3.Rows[j]["farOrderId"].ToString();
                        break;
                    }
                    else
                    {
                        rw["迄今坏品数"] = 0;
                        rw["迄今销量"] = 0;
                        rw["迄今坏品率"] = 0;
                        rw["迄今订单编号"] = "0";
                    }
                }
                datacount.Rows.Add(rw);
            }                           
            return datacount;
        }               
        
        /// <summary>
        /// 坏品等级查询
        /// </summary>
        /// <param name="grade">查询等级条件</param>
        /// <returns></returns>
        public DataTable GetData(string grade )
        {
            DataTable data = this.BadProductCount(); //获得数据集合 
            DataTable dt = data.Clone(); //查询结果集合       
            foreach (DataRow r in data.Rows)
            {
                if (Convert.ToInt32(r["60天销量"].ToString()) > 100)
                {
                    if (Convert.ToInt32(r["迄今销量"].ToString()) > 100 && (Convert.ToDouble(r["60天坏品率"].ToString()) <= 0.03 || Convert.ToInt32(r["60天坏品数"].ToString()) <= 1))
                    {
                        r["等级"] = "免检";
                    }
                    else
                    {
                        if (Convert.ToInt32(r["60天坏品率"].ToString()) <= 0.03 || Convert.ToInt32(r["60天坏品数"].ToString()) <= 2)
                        {
                            r["等级"] = "合格";
                        }
                        else
                        {
                            if (Convert.ToInt32(r["60天坏品率"].ToString()) > 0.03 && Convert.ToInt32(r["60天坏品率"].ToString()) <= 0.05)
                            {
                                r["等级"] = "待观察";
                            }
                            else
                            {
                                r["等级"] = "下架";
                            }
                        }
                    }
                }
                else
                {
                    if (Convert.ToInt32(r["迄今销量"].ToString()) > 100 && (Convert.ToDouble(r["迄今坏品率"].ToString()) <= 0.01 || Convert.ToInt32(r["迄今坏品数"].ToString()) <= 1))
                    {
                        r["等级"] = "免检";
                    }
                    else
                    {
                        if (Convert.ToInt32(r["迄今坏品率"].ToString()) <= 0.03 || Convert.ToInt32(r["迄今坏品数"].ToString()) <= 2)
                        {
                            r["等级"] = "合格";
                        }
                        else
                        {
                            if (Convert.ToInt32(r["迄今坏品率"].ToString()) > 0.03 && Convert.ToInt32(r["迄今坏品率"].ToString()) <= 0.05)
                            {
                                r["等级"] = "待观察";
                            }
                            else
                            {
                                r["等级"] = "下架";
                            }
                        }
                    }
                }
                if(grade !=""&&r["等级"].ToString()==grade)//等级查询
                {
                    dt.Rows.Add(r.ItemArray);
                }
            }
            if (grade == "")//显示全部数据
            {
                return data;
            }
            return dt;//查询结果
        }

        #endregion

        //***************************************************************************
        #region ebayfee统计表
      
        // ebayfee统计表       
        public DataTable  GetEbayFee( string ebayuserid)
        {
            DataTable dt = new DataTable();
            string Where = "";
            if (!string.IsNullOrEmpty(ebayuserid)&&ebayuserid !="")
            {
                Where=" Where e.EbayUserId='"+ebayuserid+"'";
            }
            string ebayfeetext = @"select a.id as '帐号' ,e.Payment as '支付方式',a.t1 as '一月份金额',a.t2 as '二月份金额',a.t3 as '三月份金额',
                                          a.t4 as '四月份金额',a.t5 as '五月份金额',a.t6 as '六月份金额',a.t7 as '七月份金额' ,a.t8 as '八月份金额',
	                                      a.t9 as '九月份金额',a.t10 as '十月份金额',a.t11 as '十一月份金额',a.t12 as '十二月份金额'
                                     from 
                                     (select SUM(f .ItemPrice) as price,f.EbayUserId as id,
                                      sum(case when DATEPART (MONTH ,f.CommentTime)=1 then f .ItemPrice else 0 end) as t1,
                                      sum(case when DATEPART (MONTH ,f.CommentTime)=2 then f .ItemPrice else 0 end) as t2,
                                      sum(case when DATEPART (MONTH ,f.CommentTime)=3 then f .ItemPrice else 0 end) as t3,
                                      sum(case when DATEPART (MONTH ,f.CommentTime)=4 then f .ItemPrice else 0 end) as t4,
                                      sum(case when DATEPART (MONTH ,f.CommentTime)=5 then f .ItemPrice else 0 end) as t5,
                                      sum(case when DATEPART (MONTH ,f.CommentTime)=6 then f .ItemPrice else 0 end) as t6,
                                      sum(case when DATEPART (MONTH ,f.CommentTime)=7 then f .ItemPrice else 0 end) as t7,
                                      sum(case when DATEPART (MONTH ,f.CommentTime)=8 then f .ItemPrice else 0 end) as t8,
                                      sum(case when DATEPART (MONTH ,f.CommentTime)=9 then f .ItemPrice else 0 end) as t9,
                                      sum(case when DATEPART (MONTH ,f.CommentTime)=10 then f .ItemPrice else 0 end) as t10,
                                      sum(case when DATEPART (MONTH ,f.CommentTime)=11 then f .ItemPrice else 0 end) as t11,
                                      sum(case when DATEPART (MONTH ,f.CommentTime)=12 then f .ItemPrice else 0 end) as t12    
                                     from EbayFeedBack f   group by f.EbayUserId) a 
                                     left outer join 
                                     EbayAccount e on a.id =e.EbayUserId  " +Where;
            try
            {
                dt = SqlHelper.ExecuteDataTable (SqlHelper.ceterDB_Conn, CommandType.Text, ebayfeetext, null);
            }
            catch
            {
                return  null;
            }
            return dt;
       }

        #endregion

        //***************************************************************************

        //***************************************************************************
        #region 到货商品报表

        // 到货商品报表      
        public DataTable GetArrivalGoods(string QDate)
        {

            T_ProductTrackingTableAdapter2 tAdapter = new T_ProductTrackingTableAdapter2();
            return tAdapter.GetArrivalGoodsByDate(QDate);
        }

        #endregion

        //***************************************************************************

        //***************************************************************************
        #region 缺货商品清单

        // 缺货商品清单      
        public DataTable GetLackGoods(string QDate)
        {
            T_ProductTrackingTableAdapter1 tAdapter = new T_ProductTrackingTableAdapter1();
            return tAdapter.GetLackGoodsByDate(QDate);
        }

        #endregion

        //***************************************************************************

        //***************************************************************************
        #region 库存一览表

        //库存一览表      
        public DataTable GetInventory(string pscation, string sales)
        {
            //Tvw_InventoryTableAdapter tAdapter = new Tvw_InventoryTableAdapter();
            //return tAdapter.GetInventory();
            string Where = " a1.Wh_code in ('5552','5555','5556','5559','22380','22381') ";          
            if (!string.IsNullOrEmpty(pscation.Trim()))
            {               
                if (Where.Length > 0)
                    Where += " And";
                Where += "  ProductSpecification LIKE '" + pscation + "%'";
            }
            if (!string.IsNullOrEmpty(sales.Trim()))
            {              
                if (Where.Length > 0)
                    Where += " And";
                //if (sales == "是")
                //{
                //    Where += " isnull(d.ProductId,'0') <> '0'";
                //}
                //else
                //{
                //    Where += " isnull(d.ProductId,'0') = '0'";
                //}
                Where += " c.FName= '" + sales + "' ";
            }            
//            string cmdText = @"select a.FStockID as FStockID, a.FName as FName, a.ProductId as ProductId, a.ProductName as ProductName,
//                                        a.FNumber as FNumber, a.sQty as sQty, a.FQty as FQty, 0 as Cost, p.ProductSpecification as ProductSpecification,
//                                        case when isnull(d.ProductId,'0') <> '0'  then '是' else '否' end as Sales
//                               from vw_Inventory as a left outer join dbo.vw_OnLine as d on a.ProductId= d.ProductId left outer join Product p on p.ProductId= d.ProductId 
//                               where "+Where ;
            string cmdText = @"select a1.Wh_code as FStockID,c.FName as FName,a.ProductId as ProductId,a.ProductName as ProductName,
                                '' as FNumber,ISNULL(d.SQty,0) as sQty,a1.Quantity as FQty,a1.cost as Cost,a1.Prod_code as ProductSpecification,
                                '是' as Sales
                                from [Transfer].dbo.K3_Stock as a1
                                left join CenterDB.dbo.Product as a
                                on a1.Prod_code=a.UserCode
                                left join AIS2011120420456.dbo.t_stock as c
                                on a1.Wh_code=c.FitemId
                                left join CenterDB.dbo.v_wfh as d
                                on d.productid=a1.Fnumber and a1.Wh_code=d.FStockId where " + Where;
            DataTable dt = SqlHelper.ExecuteDataTable(SqlHelper.ceterDB_Conn, CommandType.Text, cmdText, null);
            return dt;
        }
        #endregion

        //***************************************************************************

        //***************************************************************************
        #region 每日利润统计

        // 每日利润统计      
        public DataTable GetDailyBenefit(string QDate)
        {
            T_DailyBenefitTableAdapter tAdapter = new T_DailyBenefitTableAdapter();
            return tAdapter.GetDailyBenefitByDate(QDate);
        }

        #endregion

        //***************************************************************************

        //***************************************************************************
        #region 每日退款重发表

        // 每日退款重发表      
        public DataTable GetDailyRefundParcel(string Qyymm)
        {
            T_DailyRefundParcelTableAdapter tAdapter = new T_DailyRefundParcelTableAdapter();
            return tAdapter.GetDailyRefundParcelByYYMM(Qyymm);
        }

        #endregion

        //***************************************************************************

        //***************************************************************************
        #region 每日退款重发表(不分帐号)

        // 每日退款重发表(不分帐号) 
        public DataTable GetDailyAllRefundParcel(string Qyymm)
        {
            T_DailyAllRefundParcelTableAdapter tAdapter = new T_DailyAllRefundParcelTableAdapter();
            return tAdapter.GetDailyAllRefundParcelByYYMM(Qyymm);
        }

        #endregion

        //***************************************************************************

        #region ActiveListing

        //ActiveListing      
        public DataTable GetActiveListing(string ebayUserId, string ItemId, string ItemTitle, string CustomLable, string Listing, string OnLineStatus)
        {
            string Where = "";
            if (!string.IsNullOrEmpty(ebayUserId.Trim()))
            {
                if (Where.Length > 0)
                    Where += " And";
                Where += " ebayUserId='" + ebayUserId + "' ";
            }
            if (!string.IsNullOrEmpty(ItemId.Trim()))
            {
                if (Where.Length > 0)
                    Where += " And";
                Where += " ItemId='" + ItemId + "' ";
            }
            if (!string.IsNullOrEmpty(ItemTitle.Trim()))
            {
                if (Where.Length > 0)
                    Where += " And";
                Where += " ItemTitle='" + ItemTitle + "' ";
            }
            if (!string.IsNullOrEmpty(CustomLable.Trim()))
            {
                if (Where.Length > 0)
                    Where += " And";
                Where += " CustomLable='" + CustomLable + "' ";
            }
            if (!string.IsNullOrEmpty(OnLineStatus.Trim()))
            {
                if (Where.Length > 0)
                    Where += " And";
                Where += " OnLineStatus='" + OnLineStatus + "' ";
            }
            if (!string.IsNullOrEmpty(Listing.Trim()))
            {
                if (Where.Length > 0)
                    Where += " And";
                Where += " Listing='%" + Listing + "%' ";
            }
            string cmdText = @"SELECT [EbayUserId],[ItemId],[ItemTitle],[QuantityAvailable],[Purchases],[Bids],[Price],[Type],[CustomLable],[Listing]
	                                 ,[OnlineStatus],[SellingFormat],[WhCode],[ParcelType],[LogonCountry],[OnSalePrice],[Msg],ProdLevel,[DownLoadDate],[StartDate],[EndDate]  
                                 FROM [CenterDB].[dbo].[EbayActiveListing]
                                where " + Where;
            DataTable dt = SqlHelper.ExecuteDataTable(SqlHelper.ceterDB_Conn, CommandType.Text, cmdText, null);
            return dt;
        }
        #endregion

        //***************************************************************************

        #region EbaySelling

        //EbaySelling
        public DataTable GetEbaySelling(string ebayUserId, string timeFrom, string timeTo, string orderId, string BuyerId, string BuyerName, string NewOrderId, string OrderStatusId, string Standard, string rtype, string itemId, string userCode, string reason, string qtype, string resend, string memo, string ResendReason, string FZR, string DE)
        {
            string Where = "";
            if (!string.IsNullOrEmpty(OrderStatusId.Trim()))
            {                
                if (Where.Length > 0)
                {
                    Where += " And";
                }
                Where += " o.OrderStatusId = " + OrderStatusId;
            }

            if (!string.IsNullOrEmpty(itemId))
            {                
                if (Where.Length > 0)
                {
                    Where += " And ";
                }
                Where += " o.ItemId = '" + itemId + "'";
            }
            if (!string.IsNullOrEmpty(qtype))
            {                
                if (qtype == "1")
                {
                    if (Where.Length > 0)
                    {
                        Where += " And ";
                    }
                    Where += " o.SendQuantity > 0";
                }
            }
            if (!string.IsNullOrEmpty(resend))
            {
                if (resend == "true")
                {
                    Where += Where.Length > 0 ? " AND " : "";
                    Where += @" o.OrderSalesRecordNumber LIKE '%\_%' ESCAPE '\'";
                }
            }
            if (!string.IsNullOrEmpty(memo))
            {               
                if (memo == "1")
                {
                    DateTime startdate = Convert.ToDateTime(timeFrom);
                    DateTime enddate = Convert.ToDateTime(timeTo);
                    T_OrderMemoTableAdapter upmemo = new T_OrderMemoTableAdapter();
                    int val = upmemo.UpdateMemo(startdate, enddate);
                    if (Where.Length > 0)
                    {
                        Where += " And ";
                    }
                    Where += " o.memo <>'' and o.mnote  <> '手工清除标准化'";
                }
            }
            if (!string.IsNullOrEmpty(DE))
            {                
                if (DE == "1")
                {
                    if (Where.Length > 0)
                    {
                        Where += " And ";
                    }
                    Where += string.Format(@"exists(select 1 
                                                                  from [CenterDB].[dbo].[EbayActiveListing] as a                                                                    
                                                                  where o.[ItemId] = a.[ItemId] 
                                                                    and [LogonCountry]='DE'
                                                                  )
                                                           ");
                }
            }
            if (!string.IsNullOrEmpty(userCode))
            {               
                if (Where.Length > 0)
                {
                    Where += " And ";
                }
                Where += " ic.FModel LIKE '" + userCode + "%')";
            }
            if (!string.IsNullOrEmpty(FZR))
            {                
                if (Where.Length > 0)
                {
                    Where += " And ";
                }
                Where += string.Format(@"exists(select 1 
                                                                  from [AIS2011120420456].dbo.t_ICItemCustom  as c
                                                                  where ic.FItemID =c.FItemID
                                                                    and c.F_128 ='{0}'
                                                                  )
                                                        ", FZR);
            }
            if (!string.IsNullOrEmpty(ResendReason))
            {                
                if (Where.Length > 0)
                {
                    Where += " And ";
                }
                Where += " o.[ReBuilderReson] like '%" + ResendReason + "%' And o.OrderLineItemId like '%[_]%'";
            }
            if (!string.IsNullOrEmpty(reason))
            {                
                if (Where.Length > 0)
                {
                    Where += " And";
                }
                Where += " o.OrderRetentionReson LIKE '%" + reason + "%'";
            }
            if (!string.IsNullOrEmpty(ebayUserId.Trim()))
            {                
                if (Where.Length > 0)
                {
                    Where += " And";
                }
                Where += " o.EbayUserId = '" + ebayUserId + "'";
            }
            if (!string.IsNullOrEmpty(BuyerId.Trim()))
            {                
                if (Where.Length > 0)
                {
                    Where += " And";
                }
                Where += " o.UserId = '" + BuyerId + "'";
            }
            if (!string.IsNullOrEmpty(BuyerName.Trim()))
            {               
                if (Where.Length > 0)
                {
                    Where += " And";
                }
                Where += " o.BuyerFullName = '" + BuyerName + "'";
            }
            if (!string.IsNullOrEmpty(NewOrderId.Trim()))
            {              
                if (Where.Length > 0)
                {
                    Where += " And";
                }
                Where += " o.NewOrderId = '" + NewOrderId + "'";
            }
            string timeWhere = "";
            if (!string.IsNullOrEmpty(timeFrom) && !string.IsNullOrEmpty(timeTo))
            {
                if (rtype == "1")
                {
                    timeWhere = " o.Matchdate >= '" + timeFrom + "' AND o.Matchdate <= '" + timeTo + "'";
                }
                if (rtype == "2")
                {
                    timeWhere = " o.SaleDate >= '" + timeFrom + "' AND o.SaleDate <= '" + timeTo + "'";
                }
                if (rtype == "3")
                {
                    timeWhere = " o.Mtime >= '" + timeFrom + "' AND o.Mtime <= '" + timeTo + "'";
                }

            }
            if (Standard != "Select")
            {
                if (Where.Length > 0)
                {
                    Where += " And";
                }
                if (Standard == "解析正确")
                {
                    //Where += " Orders.OrderId not in (select OrderId from Orders where Standard <> '' And " + timeWhere + ")";
                    Where += " not exists (select 1 from Orders as a where a.EbayUserId = o.EbayUserId and a.OrderId = o.OrderId and a.Standard <> a.Memo)";
                }
                else if (Standard == "金额不对")
                {
                    Where += " o.Standard = '金额不对'";
                }
                else if (Standard == "在系统中找不到相应的商品信息，请修正商品信息")
                {
                    Where += " o.Standard = '在系统中找不到相应的商品信息，请修正商品信息'";
                }
                else if (Standard == "不完全代码")
                {
                    Where += " o.Standard = '不完全代码'";
                }
                else if (Standard == "发货方式为空")
                {
                    Where += " o.OrderRetentionReson = '发货方式为空'";
                }
                else if (Standard == "留言")
                {
                    Where += " o.Standard=Memo and o.Memo <> ''";
                }
                else if (Standard == "手工清除标准化")
                {
                    Where += " o.Mnote='手工清除标准化' ";
                }
                else if (Standard == "Check Country")
                {
                    Where += " o.[Standard]='Check Country'";
                }
                else if (Standard == "包含禁用产品")
                {
                    Where += " o.[Standard]='包含禁用产品'";
                }
            }
            if (!string.IsNullOrEmpty(orderId))
            {
                if (Where.Length > 0)
                    Where += " And";
                Where += " o.OrderSalesRecordNumber like '%" + orderId + "%'";
            }
            if (Where.Length > 0)
            {
                Where += " And ";
            }
            Where += timeWhere;

//            string cmdText = @"SELECT  o.[EbayUserId],o.[OrderLineItemId],[OrderId],[NewOrderId],[OrderSalesRecordNumber],ic.FModel  as 产品代码,od.Quantity as 数量,[UserId],[BuyerFullName]
//                                              ,[BuyerEmail],[ItemId],[PayPalTransactionID],[PostageService],[CustomLable],os.StatusName as 订单状态,[OrderRetentionReson],[Standard]  
//                                              ,ts.FName   as 发货仓库,[Country],[PDate]  as 付款时间,ISNULL(rbr.ReBuilderReason ,o.ReBuilderReson) as 重发原因 ,[MatchDate] as 匹配时间
//                                              ,[Memo]  as 客户备注,[BeiZhu] as 客服备注
//                                          FROM [CenterDB].[dbo].[Orders] as o
//	                                        inner join  [CenterDB].[dbo].CustomerResolve as od on o.OrderLineItemId =od.OrderLineItemId 
//                                            inner join [AIS2011120420456].dbo.t_ICItemCore as ic on od.ProductId =ic.FNumber 
//	                                        inner join [CenterDB].[dbo].[OrderStatus] as os on o.OrderStatusId =os.StatusId 
//	                                        left join [CenterDB].[dbo].[ReBuilderReason] as rbr on o.ReBuilderReson =rbr.Id  
//	                                        inner join AIS2011120420456 .dbo.t_Stock as ts on ts.FItemID =od.WarehouseId  
//                                          where " + Where;
            string cmdText = @"SELECT  o.[EbayUserId],o.[OrderLineItemId],[OrderId],[NewOrderId],[OrderSalesRecordNumber],ic.FModel  as 产品代码,od.Quantity as 数量,wfh.FQty as 库存,wfh.SQty  as 占用库存,[UserId],[BuyerFullName]
                                                      ,[BuyerEmail],[ItemId],[PayPalTransactionID],[PostageService],[CustomLable],os.StatusName as 订单状态,[OrderRetentionReson],[Standard]  
                                                      ,ts.FName   as 发货仓库,[Country],[PDate]  as 付款时间,ISNULL(rbr.ReBuilderReason ,o.ReBuilderReson) as 重发原因 ,[MatchDate] as 匹配时间
                                                      ,[Memo]  as 客户备注,[BeiZhu] as 客服备注
                                                  FROM [CenterDB].[dbo].[Orders] as o
                                                    left join  [CenterDB].[dbo].CustomerResolve as od on o.OrderLineItemId =od.OrderLineItemId 
                                                    left join [AIS2011120420456].dbo.t_ICItemCore as ic on od.ProductId =ic.FNumber 
                                                    inner join [CenterDB].[dbo].[OrderStatus] as os on o.OrderStatusId =os.StatusId 
                                                    left join [CenterDB].[dbo].[ReBuilderReason] as rbr on o.ReBuilderReson =rbr.Id  
                                                    left join AIS2011120420456 .dbo.t_Stock as ts on ts.FItemID =od.WarehouseId  
                                                    left join  [CenterDB].[dbo].[v_wfh] as wfh on wfh .productid =od.ProductId and od.WarehouseId =wfh.FStockId 
                                          where " + Where;
            DataTable dt = SqlHelper.ExecuteDataTable(SqlHelper.ceterDB_Conn, CommandType.Text, cmdText, null);
            return dt;
        }
        #endregion
    }
}
